package site.sorghum.ddl.dialect;

import site.sorghum.ddl.entity.DdlColumnWrap;
import site.sorghum.ddl.entity.DdlIndexWrap;
import site.sorghum.ddl.entity.DdlTableWrap;
import site.sorghum.ddl.enums.DbType;
import site.sorghum.ddl.field.PostgreSQLTypeConvert;
import site.sorghum.ddl.field.TypeConverter;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * PostgreSQL 数据库方言实现
 */
public class PostgreSQLDDLDialect extends AbstractDDLDialect {

    @Override
    public DbType getDatabaseType() {
        return DbType.POSTGRESQL;
    }

    @Override
    public TypeConverter typeConverter() {
        return PostgreSQLTypeConvert.INSTANCE;
    }

    @Override
    protected String quoteIdentifier(String identifier) {
        // PostgreSQL 使用双引号引用标识符，并保留大小写
        return "\"" + identifier + "\"";
    }

    @Override
    protected String quoteValue(Object value) {
        if (value == null) {
            return "NULL";
        }
        if (value instanceof String) {
            return "'" + ((String) value).replace("'", "''") + "'";
        }
        if (value instanceof Boolean) {
            return (Boolean) value ? "TRUE" : "FALSE";
        }
        return String.valueOf(value);
    }

    @Override
    protected String getColumnType(DdlColumnWrap column) {
        String typeName = column.getSqlType(typeConverter()).toLowerCase(); // PostgreSQL 类型通常是小写

        // 处理特殊类型转换
        switch (typeName) {
            case "tinyint":
                typeName = "smallint";
                break;
            case "datetime":
                typeName = "timestamp";
                break;
        }

        if (column.getSize() != null && column.getSize() > 0) {
            if (column.getDigit() != null && column.getDigit() > 0) {
                return String.format("%s(%d,%d)", typeName, column.getSize(), column.getDigit());
            }
            return String.format("%s(%d)", typeName, column.getSize());
        }
        return typeName;
    }

    @Override
    protected String getDefaultValueClause(DdlColumnWrap column) {
        if (column.getDefaultValue() != null && !column.getDefaultValue().isEmpty()) {
            return " DEFAULT " + quoteValue(column.getDefaultValue());
        }
        return "";
    }

    @Override
    protected String getColumnCommentClause(DdlColumnWrap column) {
        // PostgreSQL 列注释需要单独的 COMMENT 语句，不在列定义中
        return "";
    }

    @Override
    public List<String> generateCreateTableDDL(DdlTableWrap table) {
        List<String> result = new ArrayList<>();
        StringBuilder sql = new StringBuilder();

        // 创建表语句
        sql.append("CREATE TABLE ").append(quoteIdentifier(table.getName())).append(" (\n");

        // 添加列定义
        List<DdlColumnWrap> ddlColumnWraps = table.getColumns();
        for (int i = 0; i < ddlColumnWraps.size(); i++) {
            DdlColumnWrap column = ddlColumnWraps.get(i);
            sql.append("  ").append(quoteIdentifier(column.getName())).append(" ")
                    .append(getColumnType(column))
                    .append(getNullableClause(column))
                    .append(getDefaultValueClause(column));

            // 如果不是最后一列，添加逗号
            if (i < table.getColumns().size() - 1) {
                sql.append(",");
            }
            sql.append("\n");
        }

        // 添加主键定义
        List<String> primaryKeyColumns = new ArrayList<>();
        table.getPks().forEach(pk -> primaryKeyColumns.add(quoteIdentifier(pk)));
        if (!primaryKeyColumns.isEmpty()) {
            sql.append(",\n  PRIMARY KEY (")
                    .append(String.join(", ", primaryKeyColumns))
                    .append(")");
        }

        sql.append("\n)");


        // 添加表注释（单独的语句）
        if (table.getRemarks() != null && !table.getRemarks().isEmpty()) {
            sql.append(";\nCOMMENT ON TABLE ").append(quoteIdentifier(table.getName()))
                    .append(" IS ").append(quoteValue(table.getRemarks()));
        }

        // 添加列注释（单独的语句）
        for (DdlColumnWrap column : table.getColumns()) {
            if (column.getRemarks() != null && !column.getRemarks().isEmpty()) {
                sql.append(";\nCOMMENT ON COLUMN ").append(quoteIdentifier(table.getName()))
                        .append(".").append(quoteIdentifier(column.getName()))
                        .append(" IS ").append(quoteValue(column.getRemarks()));
            }
        }

        if (!Objects.equals(sql.charAt(sql.length() - 1), ';')) {
            sql.append(";");
        }
        result.add(sql.toString());

        for (DdlIndexWrap index : table.getIndexes()) {
            String generateIndexDDL = generateAddIndexDDL(index);
            result.add(generateIndexDDL);
        }

        return result;
    }

    @Override
    public String generateAddColumnDDL(DdlColumnWrap column) {
        String ddl = super.generateAddColumnDDL(column);

        // 添加列注释（如果需要）
        if (column.getRemarks() != null && !column.getRemarks().isEmpty()) {
            ddl += "\nCOMMENT ON COLUMN " + quoteIdentifier(column.getTable())
                    + "." + quoteIdentifier(column.getName())
                    + " IS " + quoteValue(column.getRemarks());
        }

        if (!Objects.equals(ddl.charAt(ddl.length() - 1), ';')) {
            ddl += ";";
        }
        return ddl;
    }

    @Override
    public String generateAddIndexDDL(String indexName, List<DdlColumnWrap> columnWraps, boolean unique) {
        if (columnWraps.isEmpty()) {
            return "";
        }

        String tableName = columnWraps.get(0).getTable();
        StringBuilder sql = new StringBuilder();

        if (indexName == null|| indexName.isEmpty()){
            indexName = DdlIndexWrap.columnUniqueIndexName(columnWraps.stream().map(DdlColumnWrap::getName).collect(Collectors.toList()), unique);
        }

        sql.append("CREATE ");
        if (unique) {
            sql.append("UNIQUE ");
        }
        sql.append("INDEX ")
                .append(quoteIdentifier(indexName))
                .append(" ON ")
                .append(quoteIdentifier(tableName))
                .append(" (")
                .append(columnWraps.stream()
                        .map(DdlColumnWrap::getName)
                        .map(this::quoteIdentifier)
                        .collect(Collectors.joining(", ")))
                .append(")");

        // PostgreSQL 支持索引的并发创建（可选）
        // sql.append(" CONCURRENTLY");

        // PostgreSQL 支持指定索引方法（如 btree, hash, gin, gist 等）
        // sql.append(" USING btree");

        sql.append(";");
        return sql.toString();
    }

    @Override
    public String generateDropColumnDDL(String tableName, String columnName) {
        // PostgreSQL 删除列的语法: ALTER TABLE table_name DROP COLUMN column_name
        return "ALTER TABLE " + quoteIdentifier(tableName)
                + " DROP COLUMN " + quoteIdentifier(columnName) + ";";
    }

    @Override
    public String generateDropIndexDDL(String tableName, String indexName) {
        // PostgreSQL 删除索引的语法: DROP INDEX index_name
        // 注意：PostgreSQL 不需要指定表名，因为索引名在 schema 中是唯一的
        return "DROP INDEX " + quoteIdentifier(indexName) + ";";
    }

    @Override
    public List<String> fetchAllTableNames(String schema, Function<String, List<String>> tables) {
        String sql = "SELECT table_name FROM information_schema.tables " +
                "WHERE table_type = 'BASE TABLE' AND table_schema <> 'pg_catalog' AND table_schema <> 'information_schema'";
        return tables.apply(sql);
    }

    @Override
    public DdlTableWrap fromDataSource(String schema,
                                       String tableName,
                                       Function<String, List<String>> pks,
                                       Function<String, DdlTableWrap> table,
                                       Function<String, List<DdlColumnWrap>> columns,
                                       Function<String, List<DdlIndexWrap>> indexes) {
        // 生成获取当前表信息的语句SQL
        String tableSql = String.format("SELECT TABLE_NAME    AS name\n" +
                "FROM INFORMATION_SCHEMA.TABLES\n" +
                "WHERE TABLE_NAME = '%s';", tableName);
        // 生成获取列信息的SQL
        String columnSql = String.format("SELECT '%s'                   as tableName,\n" +
                "       COLUMN_NAME              AS name,\n" +
                "       DATA_TYPE                AS dbType,\n" +
                "       CHARACTER_MAXIMUM_LENGTH AS size,\n" +
                "       NUMERIC_PRECISION        AS digit,\n" +
                "       IS_NULLABLE              AS isNullable,\n" +
                "       COLUMN_DEFAULT           AS defaultValue\n" +
                "FROM INFORMATION_SCHEMA.COLUMNS\n" +
                "WHERE TABLE_NAME = '%s';", tableName, tableName);
        // 生成主键获取信息的SQL
        String pksSql = String.format("SELECT COLUMN_NAME AS name\n" +
                "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n" +
                "WHERE TABLE_NAME = '%s'\n" +
                "  AND CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME\n" +
                "                         FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS\n" +
                "                         WHERE TABLE_NAME = '%s'\n" +
                "                           AND CONSTRAINT_TYPE = 'PRIMARY KEY');", tableName, tableName);
        // 索引信息的SQL
        String indexSql =String.format("SELECT\n" +
                "  '%s' AS tableName,\n" +
                "  I.indexname AS indexName,\n" +
                "  ARRAY_TO_STRING(\n" +
                "    ARRAY(\n" +
                "      SELECT a.attname\n" +
                "      FROM unnest(IND.indkey) k\n" +
                "      JOIN pg_attribute a ON a.attrelid = IND.indrelid AND a.attnum = k\n" +
                "      ORDER BY k\n" +
                "    ), ','\n" +
                "  ) AS columnNames,\n" +
                "  IND.indisunique AS isUnique\n" +
                "FROM pg_indexes I\n" +
                "JOIN pg_class c ON c.relname = I.indexname\n" +
                "JOIN pg_index IND ON IND.indexrelid = c.oid\n" +
                "WHERE I.tablename = '%s'\n" +
                "  AND NOT IND.indisprimary;", tableName, tableName);
        DdlTableWrap tableWrap = table.apply(tableSql);
        List<DdlColumnWrap> columnWraps = columns.apply(columnSql);
        List<String> pkList = pks.apply(pksSql);
        List<DdlIndexWrap> indexWraps = indexes.apply(indexSql);
        tableWrap.setColumns(columnWraps);
        tableWrap.setPks(pkList);
        tableWrap.setIndexes(indexWraps);
        return tableWrap;
    }
}